
import 'dart:async';
import 'dart:convert';
import 'package:flutter/cupertino.dart';
import 'package:sqflite/sqflite.dart';
import 'val_data_base_constant.dart';
import './val_data_base_frame_object.dart';

class ValDataBaseCore {
  ValDataBaseCore._init();

  static final ValDataBaseCore _instance = ValDataBaseCore._init();

  factory ValDataBaseCore() => _instance;

  final ValDataBaseEngine _engine = ValDataBaseEngine();


  Future<void> setup() async {
    await _engine.setup();
  }

  /// 打开数据库
  static Future<void> openDb() async {
    await  _instance._engine.openDb();
  }

  /// 关闭
  static void closeDb() {
    _instance._engine.closeDb();
  }

  /// 删表
  static Future<void> deleteTable(String dbName) async {
    await  _instance._engine.deleteTable(dbName);
  }

  /// 清空
  static Future<void> cleanTable(String dbName , ValueChanged<bool> completion) async {
    await _instance._engine.cleanTable(dbName ,completion );
  }

  /// 保存单个数据
  static Future<void> saveObject(ValDataBaseFrameObject object) async {
    await _instance._engine.saveObject(object.dbTableName, object);
  }

  static Future<void> addIndex(ValDataBaseFrameObject object) async {
    await _instance._engine.addQueryIndex(object.dbTableName, object);
  }

  /// 保存多个数据
  static void saveObjects(List <ValDataBaseFrameObject> objects) {
    for (var element in objects) {
      saveObject(element);
    }
  }

  /// 读取数据返回为List<Map>,
  static void getObjects(
      String dbName,
      ValueChanged<List<Map>> completion,{
        List <VaDataBaseQuery>? query,
        String? orderKey,
        bool isDesc = true,
        int? limitCount,
      }
      ) async {
    _instance._engine.getObject(dbName, query ?? [],
        completion,
        orderKey: orderKey,
        isDesc: isDesc,
        limitCount: limitCount
    );
  }

  /// 删除数据
  static Future<bool> delete(
      String dbName,
      List <VaDataBaseQuery> conditions,
      ) async {
    Completer<bool> completer = Completer();
    _instance._engine.deleteRow(dbName, conditions, (value) => completer.complete(value));
    return completer.future;
  }

  /// 初始化对象db，主要为了自定义对象存储的建表
  Future<void> setupObjectDb(ValDataBaseFrameObject object) async {
    await _engine.createTable(object.dbTableName,object);
  }

}


/// 核心类
class ValDataBaseEngine {

  Database? _database;

  Future<void> setup() async {
    await openDb();
  }

  /// 初始化
  Future<void> openDb() async {
    String databasesPath = await getDatabasesPath();
    String savePath = '$databasesPath/${ValDataBaseConstant.dbName}';
    await _safeOperate(() async {
      _database = await openDatabase(
        savePath,
        version: ValDataBaseConstant.version,
        onOpen: (Database db) async {
          _database = db;
        },
        onCreate: (Database db, int version) async {
          _database = db;
        },
      );
    }, '打开数据库失败');
  }

  /// 关闭
  void closeDb() async {
    await _safeOperate(() async {
      await _database?.close();
    }, '关闭数据库失败');
  }

  /// 删表
  Future<void> deleteTable(String dbName) async {

    await Future.wait([
      _database!.delete(dbName),
    ]);
  }

  /// 清空
  Future<void> cleanTable(
      String dbName,
      ValueChanged<bool> completion) async {

    await _safeOperate(() async {
      await _database?.transaction((txn) async {
        txn.delete(dbName);
        completion(true);
      });
    }
        ,'插入数据失败');
  }

  /// 删除条件数据
  Future<void> deleteRow(
      String dbName,
      List <VaDataBaseQuery> conditions,
      ValueChanged<bool> completion) async {

    await _safeOperate(() async {
      await _database?.transaction((txn) async {
        String sql = '$dbName WHERE ';
        String conditionSql = '';
        for (int i =0; i < conditions.length; i++) {
          VaDataBaseQuery query =  conditions[i];
          conditionSql += '${query.key} = ${query.value}';
          if (i != conditions.length - 1)  {
            conditionSql += ' ${query.isAnd ? 'AND' : 'OR'} ';
          }
        }
        sql += conditionSql;
        txn.delete(sql);
        completion(true);
      });
    }
    ,'删除数据失败');
  }

  /// 建表
  Future<void> createTable(String dbTabName, ValDataBaseFrameObject object) async {
    await Future.wait([
      _createConversationTable(dbTabName,object),
    ]);
  }

  Future _createConversationTable(String dbTabName, ValDataBaseFrameObject object) async {
    await _safeOperate(() async {

      String sql = 'CREATE TABLE IF NOT EXISTS $dbTabName (';
      String primarySql = 'id INTEGER PRIMARY KEY AUTOINCREMENT,';
      primarySql = '${object.primaryKey} TEXT PRIMARY KEY,';
          /// 自定义健
      Map<String, ValDataBaseQueryEnum>? customTableKeys = object.queryKeys;
      String customSql = '';
      customTableKeys?.forEach((key, value) {
        if (value == ValDataBaseQueryEnum.text) {
          customSql += '$key TEXT,';
        }else if (value == ValDataBaseQueryEnum.int) {
          customSql += '$key INTEGER,';
        }else if (value == ValDataBaseQueryEnum.none) {
          customSql += '$key INTEGER,';
        }else if (value == ValDataBaseQueryEnum.data) {
          customSql += '$key BLOB,';
        }
      });
      customSql += 'data TEXT);';

      /// 建表
      sql += primarySql;
      sql += customSql;
      await _database?.execute(sql);

    }, '建$dbTabName表失败 dbname = $dbTabName');
  }

  /// 添加缩影
  Future addQueryIndex(String dbTabName, ValDataBaseFrameObject object) async {
    /// 索引
    if (object.indexKeys != null && object.indexKeys!.isNotEmpty) {
      await _safeOperate(() async {
        String sql = 'CREATE INDEX IF NOT EXISTS ${object.indexNameKey} ON ${object.dbTableName} ${object.indexKeys!.toString()}';
        sql = sql.replaceAll('[', '(');
        sql = sql.replaceAll(']', ')');
        await _database?.execute(sql);
      }, '添加索引失败 dbname = $dbTabName ');
    }

  }


  /// 存
  Future<void> saveObject(String dbTabName ,ValDataBaseFrameObject object) async {
    await _safeOperate(() async {
      await _database?.transaction((txn) async {
        int count = Sqflite.firstIntValue(await txn.rawQuery(
              'SELECT COUNT(*) FROM $dbTabName WHERE ${object.primaryKey} = "${object.queryValues?[object.primaryKey]}"')) ??
              0;
          /// 更新
        if (count > 0) {
          String sql = 'UPDATE $dbTabName SET data = ? WHERE ${object.primaryKey} = "${object.queryValues?[object.primaryKey]}"';
          await txn.rawUpdate(sql,[jsonEncode(object.dbData)]);

          /// 插入
        } else {
          String sql = 'INSERT INTO $dbTabName ';

          /// key
          List <String> keys = [object.primaryKey];
          if (object.queryKeys != null) {
            keys.addAll(object.queryKeys!.keys);
          }
          keys.add('data');
          String keySql = keys.toList().toString();


          /// value
          String formatSql = ' values ';
          List <String> format = [];
          List <Object> values = [];
          for (int i = 0; i < keys.length; i++) {
            format.add('?');
            String key = keys[i];
            if (key != 'data') {
              dynamic value =  object.queryValues?[key];
              value ??= object.dbData[key];
          values.add(value ?? '');
        }
      }

      values.add(jsonEncode(object.dbData));

          /// sql
          sql += keySql;
          sql += formatSql;
          sql += format.toList().toString();
          sql = sql.replaceAll('[', '(');
          sql = sql.replaceAll(']', ')');
          txn.rawInsert(sql,values);
        }

      });
    }, '保存失败 dbname = $dbTabName');
  }

  /// 读
  Future<void> getObject(
      String dbName,
      List <VaDataBaseQuery> conditions,
      ValueChanged<List<Map>> completion, {
      String? orderKey,
      bool isDesc = true,
      int? limitCount,
  }) async
  {
    /// search condition
    String sql = 'SELECT * FROM $dbName';

    if (conditions.isNotEmpty) {
      sql += ' WHERE ';
    }
    String conditionSql = '';
    for (int i =0; i < conditions.length; i++) {
      VaDataBaseQuery query =  conditions[i];
      if (query.type == ValDataBaseQueryEnum.text) {
        conditionSql += '${query.key} = "${query.value}"';
      }else {
        conditionSql += '${query.key} = ${query.value}';
      }
      if (i != conditions.length - 1)  {
        conditionSql += ' ${query.isAnd ? 'AND' : 'OR'} ';
      }
    }
    sql +=  conditionSql;
    /// ORDER
    if (orderKey != null) {
      sql += ' ORDER BY $orderKey ${isDesc ? 'DESC' : 'ASC'}';
    }
    /// LIMIT COUNT
    if (limitCount != null) {
      sql += ' LIMIT $limitCount';
    }

    await _safeOperate(() async {
      await _database?.transaction((txn) async {
        List<Map> result = await txn.rawQuery(sql);
        result = result.map((e)  {
          if (e['data'] is String) {
            return (jsonDecode((e['data'])) ?? {}) as Map;
          }
          return e;
        }).toList();
        completion(result);
      });
    },'读取数据失败-- dbname = $dbName , sql = $sql');
  }


  Future _safeOperate(Future Function() operate, String errorMsg) async {
    try {
      await operate();
    } catch (e) {
      print(errorMsg);
    }
  }
}